﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.Common;

namespace AFCEPF.BankApplication.DAL
{
    public abstract class AbstractDbAccessor
    {
        private string                  _connectionString;
        private DataMapper              _mappedObject;
        protected AbstractSqlGenerator  _sqlGenerator;

        private IDbConnection           _connection;
        private IDbCommand              _command;
        private IDbTransaction          _transaction;
        private IDbDataAdapter          _adapter;






        /// <summary>
        /// ctor
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="mappedObject">The mapped object.</param>
        protected AbstractDbAccessor(string connectionString, DataMapper mappedObject)
        {
            _connectionString = connectionString;
            _mappedObject = mappedObject;

            // Type fortement les objets DAO en
            //      - Sql Server
            //   ou
            //      - MySql
            initTypedDbObjects();
        }


        /// <summary>
        /// Inits the strongly-typed db objects
        /// (for Sql Server, or My Sql).
        /// </summary>
        protected abstract void initTypedDbObjects();

        /// <summary>
        /// Gets a new connection.
        /// </summary>
        /// <returns>a new connection.</returns>
        protected abstract IDbConnection newConnection();

        /// <summary>
        /// Gets a new Command.
        /// </summary>
        /// <returns>a new Command.</returns>
        protected abstract IDbCommand newCommand();
        


        /// <summary>
        /// Deletes the record for the specified Id
        /// and returns true if the number of affected
        /// records is 1, else false.
        /// </summary>
        /// <returns>true on success.</returns>
        public bool Delete()
        {
            bool result;
            string sqlCommand = SqlGenerator.GenerateSqlFor_Delete();

            IDbTransaction transaction = getActiveTransaction();
            
            result = ExecuteCommandNonQuery(sqlCommand, 
                                            transaction, 
                                            true);
            

            return result;
        }

        /// <summary>
        /// Updates the current  instance in thge database
        /// </summary>
        /// <returns>true on success</returns>
        public bool Update()
        {
            bool result;            
            string sqlCommand = SqlGenerator.GenerateSqlFor_Update();

            IDbTransaction transaction = getActiveTransaction();

            result = ExecuteCommandNonQuery(sqlCommand,
                                            transaction,
                                            true);

            return result;
        }

        /// <summary>
        /// Inserts this instance in the database
        /// </summary>
        /// <returns>
        /// the last generated primary key
        /// value</returns>        
        public int Insert()
        {
            int lastGeneratedID = -1;

            IDbTransaction transaction = getActiveTransaction();
            
            // Executes the INSERT statement.
            bool result         = false;
            string sqlCommand   = SqlGenerator.GenerateSqlFor_Insert();
            result              = ExecuteCommandNonQuery(sqlCommand,
                                                         transaction,
                                                         false);

            if (result)
            {
                // Generates the command to get
                // back the last generated primary 
                // key value..
                IDbCommand command  = newCommand();
                command.CommandType = CommandType.Text;
                command.Connection  = Connection;
                command.CommandText = SqlGenerator.getSqlForLastGeneratedID();
                command.Transaction = transaction;

                // Executes the command and get back
                // the last generated Id..
                lastGeneratedID = Int32.Parse(command.ExecuteScalar().ToString());

                transaction.Commit();
                Connection.Close();
            }
            return lastGeneratedID;
        }


        /// <summary>
        /// Returns a single-record DataTable 
        /// containing the record for the current
        /// instance's primary key value.
        /// </summary>
        /// <returns>A single-record DataTable</returns>        
        public virtual DataTable Select()
        {
            // Open the connection.
            Connection.ConnectionString = ConnectionString;
            Connection.Open();

            // Create a SqlCommand to retrieve table data.
            Command.CommandText = SqlGenerator.GenerateSqlFor_Select();
            Command.Connection  = Connection;
            Command.CommandType = CommandType.Text;

            // Set the SqlDataAdapter's SelectCommand.
            Adapter.SelectCommand = Command;

            // Fill the DataSet.
            DataSet dataSet = new DataSet("ResultDataset");
            Adapter.Fill(dataSet);

            Connection.Close();

            return dataSet.Tables[0];
        }



        /// <summary>
        /// Executes the command non query.
        /// </summary>
        /// <param name="commandText">The command text.</param>
        /// <returns>true on success</returns>
        protected bool ExecuteCommandNonQuery(string commandText,
                                              IDbTransaction transaction,
                                              bool commitAndClose)
        {
            int recordsAffected = 0;

            try
            {               

                // Command..
                Command.CommandType = CommandType.Text;
                Command.Connection  = transaction.Connection;
                Command.CommandText = commandText;
                Command.Transaction = transaction;


                // On exécute notre Command en mode
                // ExecuteNonQuery, et l'on récupère 
                // le nb de records affectés, qui doit 
                // tjs être 1.
                //Console.WriteLine("Executing : {0}", commandText );

                recordsAffected = Command.ExecuteNonQuery();

                // On Commit la transaction si besoin..
                if (commitAndClose)
                {
                    transaction.Commit();
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                // on fait un RollBack de notre transaction.
                transaction.Rollback();
            }
            finally
            {
                if (Connection.State == ConnectionState.Open
                    && commitAndClose)
                {
                    Connection.Close();
                }
            }

            return (recordsAffected == 1);

        }

        /// <summary>
        /// returns the active transaction on
        /// the Connection
        /// </summary>
        /// <returns>an active transaction</returns>
        private IDbTransaction getActiveTransaction()
        {
            Connection.ConnectionString = ConnectionString;
            Connection.Open();
            IDbTransaction transaction = Connection.BeginTransaction();

            return transaction;
        }



        /*****************************************************
         * Properties.
         ****************************************************/

        /// <summary>
        /// Gets or sets the transaction.
        /// </summary>       
        public IDbTransaction Transaction
        {
            get { return _transaction; }
            protected set { _transaction = value; }
        }

        /// <summary>
        /// Gets the SQL generator.
        /// </summary>
        public AbstractSqlGenerator SqlGenerator
        {
            get { return _sqlGenerator; }
        }


        /// <summary>
        /// Gets the mapped object.
        /// </summary>
        public DataMapper MappedObject
        {
            get { return _mappedObject; }
        }

        /// <summary>
        /// Gets the connection string.
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }

        /// <summary>
        /// Gets or sets the command.
        /// </summary>
        public IDbCommand Command
        {
            get { return _command; }
            protected set { _command = value; }
        }

        /// <summary>
        /// Gets or sets the connection.
        /// </summary>
        public IDbConnection Connection
        {
            get { return _connection; }
            protected set { _connection = value; }
        }

        /// <summary>
        /// Gets or sets the adapter.
        /// </summary>        
        public IDbDataAdapter Adapter
        {
            get { return _adapter; }
            set { _adapter = value; }
        }

    }
}
